﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using sqlserverextensions.ExportOperations;


public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure()]
    public static void QueryToExcelXml(SqlString fileName,
                                       SqlString sqlStatement,
                                       SqlBoolean multiQuery,
                                       SqlBoolean columnHeader)
    {

        //' Validieren der Übergabe-Parameter
        ExportHelper.ValidateMandatoryParameters(fileName, sqlStatement, multiQuery, columnHeader);

        if (multiQuery)
        {
            DataSet dataSet = ExportHelper.GetDataSet(sqlStatement.ToString());

            ExcelXml excXml = new ExcelXml(fileName.ToString());

            excXml.WorkbookStart();

            foreach (DataTable dt in dataSet.Tables)
            {
                excXml.WorksheetStart(dt.TableName);
                ExportHelper.Export2ExcelXml(dt, ref excXml, columnHeader.Value);
                excXml.WorksheetEnd();
            }

            excXml.WorkbookEnd();

            excXml.Close();
            excXml.Dispose();
        }
        else
        {
            using (SqlConnection connection = new SqlConnection("context connection=true"))
            {
                connection.Open();

                SqlDataReader dataReader = ExportHelper.GetDataReader(connection, sqlStatement.ToString());

                ExcelXml excXml = new ExcelXml(fileName.ToString());

                excXml.WorkbookStart();
                ExportHelper.Export2ExcelXml(ref dataReader, ref excXml, columnHeader.Value);
                excXml.WorkbookEnd();

                excXml.Close();
                excXml.Dispose();

                dataReader.Close();
                connection.Close();
            }
        }

    }
 };
